package com.itx.gatewayx.utils;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * SQL执行工具类
 */
@Slf4j
@Component
public class SqlExecutor {

    @Resource
    private DataSource dataSource;

    /**
     * 启动时执行SQL脚本
     */
    @PostConstruct
    public void initDatabase() {
        log.info("开始执行数据库初始化脚本");
        try {
            // 修改News表的content字段为可为空或设置默认值
            updateNewsContentColumn();
            // executeSqlScript("db/add_uniquekey_to_news.sql");
            log.info("数据库初始化脚本执行完成");
        } catch (Exception e) {
            log.error("数据库初始化脚本执行失败", e);
        }
    }
    
    /**
     * 更新News表的content字段，设置为可为空或添加默认值
     */
    private void updateNewsContentColumn() {
        try (Connection connection = dataSource.getConnection();
             Statement stmt = connection.createStatement()) {
            
            // 检查数据库中是否存在news表
            boolean tableExists = false;
            ResultSet tables = connection.getMetaData().getTables(null, null, "news", null);
            if (tables.next()) {
                tableExists = true;
            }
            
            if (!tableExists) {
                log.info("news表不存在，跳过修改");
                return;
            }
            
            // 检查content字段是否存在并需要修改
            boolean fieldNeedsModification = false;
            ResultSet columns = connection.getMetaData().getColumns(null, null, "news", "content");
            if (columns.next()) {
                String nullable = columns.getString("IS_NULLABLE");
                if ("NO".equals(nullable)) {
                    fieldNeedsModification = true;
                }
            }
            
            if (fieldNeedsModification) {
                log.info("修改news表的content字段为可为空");
                stmt.execute("ALTER TABLE news MODIFY COLUMN content TEXT NULL");
                log.info("成功修改news表的content字段");
            } else {
                log.info("news表的content字段已经是可为空的，无需修改");
            }
            
        } catch (SQLException e) {
            log.error("修改news表的content字段失败", e);
            throw new RuntimeException("修改数据库字段失败", e);
        }
    }

    /**
     * 执行SQL脚本
     *
     * @param scriptPath 脚本路径（相对于类路径）
     */
    public void executeSqlScript(String scriptPath) {
        try {
            // 从类路径加载SQL脚本
            InputStream inputStream = getClass().getClassLoader().getResourceAsStream(scriptPath);
            if (inputStream == null) {
                log.error("找不到SQL脚本文件: {}", scriptPath);
                return;
            }

            // 读取SQL脚本内容
            String sqlScript = new BufferedReader(
                    new InputStreamReader(inputStream, StandardCharsets.UTF_8))
                    .lines()
                    .collect(Collectors.joining("\n"));

            // 分割SQL语句
            List<String> sqlStatements = getSqlStatements(sqlScript);

            // 执行SQL语句
            try (Connection connection = dataSource.getConnection()) {
                // 关闭自动提交
                boolean autoCommit = connection.getAutoCommit();
                connection.setAutoCommit(false);

                try (Statement statement = connection.createStatement()) {
                    for (String sql : sqlStatements) {
                        try {
                            log.info("执行SQL: {}", sql);
                            statement.execute(sql);
                        } catch (SQLException e) {
                            log.error("执行SQL语句失败: {}", sql, e);
                            throw e;
                        }
                    }
                    connection.commit();
                } catch (SQLException e) {
                    connection.rollback();
                    throw e;
                } finally {
                    connection.setAutoCommit(autoCommit);
                }
            }
        } catch (Exception e) {
            log.error("执行SQL脚本{}失败", scriptPath, e);
            throw new RuntimeException("执行SQL脚本失败", e);
        }
    }

    /**
     * 将SQL脚本分割为多个SQL语句
     *
     * @param sqlScript SQL脚本
     * @return SQL语句列表
     */
    private List<String> getSqlStatements(String sqlScript) {
        List<String> statements = new ArrayList<>();
        StringBuilder currentStatement = new StringBuilder();
        boolean inString = false;
        boolean inComment = false;
        boolean inSingleLineComment = false;

        for (int i = 0; i < sqlScript.length(); i++) {
            char c = sqlScript.charAt(i);
            char next = (i < sqlScript.length() - 1) ? sqlScript.charAt(i + 1) : '\0';

            // 处理注释
            if (!inString) {
                if (!inComment && !inSingleLineComment && c == '-' && next == '-') {
                    inSingleLineComment = true;
                    i++;
                    continue;
                }
                if (inSingleLineComment && (c == '\n' || c == '\r')) {
                    inSingleLineComment = false;
                    continue;
                }
                if (inSingleLineComment) {
                    continue;
                }
                if (!inComment && c == '/' && next == '*') {
                    inComment = true;
                    i++;
                    continue;
                }
                if (inComment && c == '*' && next == '/') {
                    inComment = false;
                    i++;
                    continue;
                }
                if (inComment) {
                    continue;
                }
            }

            // 处理字符串
            if (c == '\'' && !inComment && !inSingleLineComment) {
                inString = !inString;
            }

            // 处理语句分隔符
            if (c == ';' && !inString && !inComment && !inSingleLineComment) {
                String statement = currentStatement.toString().trim();
                if (!statement.isEmpty()) {
                    statements.add(statement);
                }
                currentStatement = new StringBuilder();
                continue;
            }

            currentStatement.append(c);
        }

        // 添加最后一个语句
        String lastStatement = currentStatement.toString().trim();
        if (!lastStatement.isEmpty()) {
            statements.add(lastStatement);
        }

        return statements.stream()
                .filter(s -> !s.isEmpty())
                .collect(Collectors.toList());
    }
} 